<?php
$table = 'Clubs';
include('includes/before_html.php');
if ($friend_page)
    checkSecurity($table);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        <meta name="description" content="" />
        <meta name="keywords" content="" />
        <meta name="author" content="" />
        <?php include('includes/head.php'); ?>
        <title><?php echo $heading ?></title>

    </head>

    <body>
        <div id="wrapper">
            <?php include('includes/header.php'); ?>
            <?php include('includes/nav.php'); ?>
            <div id="content">
                <?php
                #Club President
                echo "<h3>" . $getName . "'s Presidential Clubs</h3><BR>";
                $db = mysql_connect(db_server, $db_user, $db_password);
                mysql_select_db($db_dbname);
                $sql = "SELECT C.Name, C.Description, C.MeetingTimes 
		        FROM Clubs C 
			WHERE C.President = " . $getRuid;
                echo "SQL QUERY:&nbsp;" . $sql . "<BR><BR>";
                $result2 = mysql_query($sql);
                $count = mysql_num_rows($result2);

                if ($count == 0) {
                    echo "Not a President of any clubs.";
                }
                echo "<table>";
                while ($row = mysql_fetch_array($result2)) {
                    #inserted
                    #Friends in club
                    $sql_friends = "SELECT C.Name, COUNT(C.Name) AS Num
		                    FROM Clubs C, ClubMembers M
		        		WHERE C.Name=M.ClubName
					      AND C.Name = '" . $row['Name'] . "' 
		        		      AND M.RUID IN (SELECT DISTINCT U.RUID 
								   FROM Friends F, Users U
		    	                                           WHERE F.Person1=" . $getRuid . " AND 
		    	                                           F.Person2=U.RUID AND 
			                                           F.Status='Accepted')
				   GROUP BY C.Name";
                    $result_friends = mysql_query($sql_friends);
                    $row_friends = mysql_fetch_array($result_friends);
                    #People in Club
                    $sql_attend = "SELECT C.Name, COUNT(C.Name) AS Num
			           FROM Clubs C, ClubMembers M
			           WHERE C.Name=M.ClubName
				         AND C.Name = '" . $row['Name'] . "' 
			           GROUP BY M.ClubName";

                    $result_attend = mysql_query($sql_attend);
                    $row_attend = mysql_fetch_array($result_attend);
                    #inserted

                    $result = mysql_query("describe $table");
                    echo "<table>";
                    {
                        echo "<tr><td><strong> Club: " . $row['Name'] . " </strong></td></tr>";
                        if ($row_attend['Num'] == null)
                            echo "<tr><td> Club Members: 0</td></tr>";
                        else
                            echo "<tr><td> Club Members: " . $row_attend['Num'] . " </td></tr>";
                        if ($row_friends['Num'] == null)
                            echo "<tr><td> Friends in Club: 0</td></tr>";
                        else
                            echo "<tr><td> Friends in Club: " . $row_friends['Num'] . " </td></tr>";
                        echo "<tr><td> Description: " . $row['Description'] . " </td></tr>";
                        echo "<tr><td> Meeting Times: " . $row['MeetingTimes'] . " </td></tr>";
                    }
                    echo "</table><BR>";
                }
                echo "</table><BR>";

                #Club Member
                echo "<h3>" . $getName . "'s $table</h3><BR>";
                $db = mysql_connect(db_server, $db_user, $db_password);
                mysql_select_db($db_dbname);
                $sql = "SELECT C.Name, C.Description, U.FirstName, U.LastName, C.MeetingTimes FROM Clubs C, ClubMembers M, Users U WHERE M.RUID = " . $getRuid . " AND M.ClubName=C.Name AND C.President = U.RUID";
                echo "SQL QUERY:&nbsp;" . $sql . "<BR><BR>";
                $result2 = mysql_query($sql);
                $count = mysql_num_rows($result2);

                if ($count == 0) {
                    echo "Not a member of any clubs";
                }
                echo "<table>";
                while ($row = mysql_fetch_array($result2)) {
                    #Friends in club
                    $sql_friends = "SELECT C.Name, COUNT(C.Name) AS Num
		                    FROM Clubs C, ClubMembers M
		        		WHERE C.Name=M.ClubName
					      AND C.Name = '" . $row['Name'] . "' 
		        		      AND M.RUID IN (SELECT DISTINCT U.RUID 
								   FROM Friends F, Users U
		    	                                           WHERE F.Person1=" . $getRuid . " AND 
		    	                                           F.Person2=U.RUID AND 
			                                           F.Status='Accepted')
				   GROUP BY C.Name";
                    $result_friends = mysql_query($sql_friends);
                    $row_friends = mysql_fetch_array($result_friends);
                    #People in Club
                    $sql_attend = "SELECT C.Name, COUNT(C.Name) AS Num
			           FROM Clubs C, ClubMembers M
			           WHERE C.Name=M.ClubName
				         AND C.Name = '" . $row['Name'] . "' 
			           GROUP BY M.ClubName";

                    $result_attend = mysql_query($sql_attend);
                    $row_attend = mysql_fetch_array($result_attend);
                    #inserted
                    echo "<table>";
                    {
                        echo "<tr><td><strong> Club: " . $row['Name'] . " </strong></td></tr>";
                        if ($row_attend['Num'] == null)
                            echo "<tr><td> Club Members: 0</td></tr>";
                        else
                            echo "<tr><td> Club Members: " . $row_attend['Num'] . " </td></tr>";
                        if ($row_friends['Num'] == null)
                            echo "<tr><td> Friends in Club: 0</td></tr>";
                        else
                            echo "<tr><td> Friends in Club: " . $row_friends['Num'] . " </td></tr>";
                        echo "<tr><td> President: " . $row['FirstName'] . " " . $row['LastName'] . " </strong></td></tr>";
                        echo "<tr><td> Description: " . $row['Description'] . " </td></tr>";
                        echo "<tr><td> Meeting Times: " . $row['MeetingTimes'] . " </td></tr>";
                    }
                    echo "</table><BR>";
                }
                echo "</table>";

                #Suggested Clubs to Join
                echo "<h3>" . $getName . "'s Suggested Clubs to Join</h3><BR>";
                echo "Based off clubs of mutual friends";
                $db = mysql_connect(db_server, $db_user, $db_password);
                mysql_select_db($db_dbname);

                $sql = "SELECT DISTINCT C.Name, C.Description, U1.FirstName, U1.LastName, C.MeetingTimes 
		        FROM Clubs C, ClubMembers M, Users U1
		        WHERE C.Name=M.ClubName AND 
			      C.President=U1.RUID AND
			      M.RUID IN (SELECT DISTINCT U.RUID 
					 FROM Friends F, Users U 
					 WHERE F.Person1= " . $getRuid . " AND 
					       F.Person2=U.RUID 
					       AND F.Status='Accepted') AND
			      C.Name NOT IN (SELECT DISTINCT C1.Name
			                     FROM Clubs C1, ClubMembers M1
			                     WHERE M1.RUID = " . $getRuid . " AND 
						 M1.ClubName=C1.Name)";

                echo "SQL QUERY:&nbsp;" . $sql . "<BR><BR>";
                $result2 = mysql_query($sql);
                $count = mysql_num_rows($result2);

                if ($count == 0) {
                    echo "No suggested clubs";
                }
                echo "<table>";
                while ($row = mysql_fetch_array($result2)) {
                    #Friends in club
                    $sql_friends = "SELECT C.Name, COUNT(C.Name) AS Num
		                    FROM Clubs C, ClubMembers M
		        		WHERE C.Name=M.ClubName
					      AND C.Name = '" . $row['Name'] . "' 
		        		      AND M.RUID IN (SELECT DISTINCT U.RUID 
								   FROM Friends F, Users U
		    	                                           WHERE F.Person1=" . $getRuid . " AND 
		    	                                           F.Person2=U.RUID AND 
			                                           F.Status='Accepted')
				   GROUP BY C.Name";
                    $result_friends = mysql_query($sql_friends);
                    $row_friends = mysql_fetch_array($result_friends);
                    #People in Club
                    $sql_attend = "SELECT C.Name, COUNT(C.Name) AS Num
			           FROM Clubs C, ClubMembers M
			           WHERE C.Name=M.ClubName
				         AND C.Name = '" . $row['Name'] . "' 
			           GROUP BY M.ClubName";

                    $result_attend = mysql_query($sql_attend);
                    $row_attend = mysql_fetch_array($result_attend);
                    #inserted
                    echo "<table>";
                    {
                        echo "<tr><td><strong> Club: " . $row['Name'] . " </strong></td></tr>";
                        if ($row_attend['Num'] == null)
                            echo "<tr><td> Club Members: 0</td></tr>";
                        else
                            echo "<tr><td> Club Members: " . $row_attend['Num'] . " </td></tr>";
                        if ($row_friends['Num'] == null)
                            echo "<tr><td> Friends in Club: 0</td></tr>";
                        else
                            echo "<tr><td> Friends in Club: " . $row_friends['Num'] . " </td></tr>";
                        echo "<tr><td> President: " . $row['FirstName'] . " " . $row['LastName'] . " </strong></td></tr>";
                        echo "<tr><td> Description: " . $row['Description'] . " </td></tr>";
                        echo "<tr><td> Meeting Times: " . $row['MeetingTimes'] . " </td></tr>";
                    }
                    echo "</table><BR>";
                }
                echo "</table>";

                mysql_close($db)
                ?>

                <!--Join a Club-->
                <h3>Join a club</h3>
                <form action="clubs.php" method="post">
                    *ClubName: <input type="text" name="j_name" /> <br>
                        <input type="submit" />
                </form>

                <?php
                if (!empty($_POST['j_name'])) {
                    $db = mysql_connect(db_server, $db_user, $db_password);
                    mysql_select_db($db_dbname);
                    $sql = "INSERT INTO ClubMembers VALUES ('" . $_POST['j_name'] . "',
			                                           " . $getRuid . ")";
                    echo "Joined Club!";

                    echo $sql;
                    mysql_query($sql);

                    mysql_close($db);
                }
                ?>


                <!--Create a Club-->
                <h3>Create a Club</h3>
                <form action="clubs.php" method="post">
                    *Club Name: <input type="text" name="ClubName" /> <br></br>
                    *Meeting Time: <input type="text" name="Time" /><br></br>
                    *Description: <input type="text" name="Description" /><br></br>
                    <input type="submit" />
                </form>

                <?php
                if (!empty($_POST['ClubName'])) {
                    $db = mysql_connect(db_server, $db_user, $db_password);
                    mysql_select_db($db_dbname);
                    $sql = "INSERT INTO Clubs VALUES  ('" . $_POST['ClubName'] . "',
							'" . $_POST['Description'] . "',
							'" . $_POST['Time'] . " ',
							" . $getRuid . ")";
                    echo "Club Created!";

                    echo $sql;
                    mysql_query($sql);
                    $sql = "INSERT INTO ClubMembers VALUES ('" . $_POST['ClubName'] . "'," . $getRuid . ")";
                    echo $sql;
                    mysql_query($sql);

                    mysql_close($db);
                }
                ?>

            </div> <!-- end #content -->
<?php include('includes/sidebar.php'); ?>
	    <?php include('includes/footer.php'); ?>
        </div> <!-- End #wrapper -->
    </body>
</html>
